﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace vgo_vs01.Models.KetNoi
{
    public class ConnectDB
    {
        string connectionString ="";
        public SqlConnection conn;
        public SqlCommand command;
        public SqlDataReader dataRead;
        public SqlDataAdapter dataAdap;

        public ConnectDB() 
        {
            //connectionString = @"Data Source=VGO06-PC\SQLEXPRESS; Initial Catalog=vgo_db01; User ID = sa; password=123456sa";
            //connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringData"].ConnectionString;
            connectionString = ConfigurationManager.AppSettings["ConnectionStringModel"];
        }
        //Khởi tạo
        public ConnectDB(string strConnection) 
        {
            this.connectionString = strConnection;
        }
        //Mở kết nối
        public void ConnectData() 
        {
            if(conn == null) conn = new SqlConnection();
            if (dataAdap == null) dataAdap = new SqlDataAdapter();
            if(conn.State == ConnectionState.Closed)
            {
                conn.ConnectionString = connectionString;
                conn.Open();
            }
            if(command == null)
            {
                command = new SqlCommand();
                command.Connection = conn;
                command.CommandTimeout = 120;
                command.CommandType = CommandType.Text;
            }
        }
        //Đóng kết nối
        public void CloseData() 
        {
            if(dataRead != null)
            {
                dataRead.Close();
            }
            if(conn != null)
            {
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            dataRead = null;
            conn = null;
        }
        //Chạy câu lệnh ExecuteReader(trả về hoặc không trả về dữ liệu)
        public void ExeReader(string querySrtring) 
        {
            if (conn == null) ConnectData();
            if (dataRead != null) dataRead.Close();
            command.CommandText = querySrtring;
            dataRead = command.ExecuteReader();
        }
        //Chạy câu lệnh ExecuteNonQuery(Không trả về dữ liệu, lưu ý đóng kết nối)
        public void ExeNonQuery(string queryString) 
        {
            if (conn == null) ConnectData();
            if (dataRead != null) dataRead.Close();
            command.CommandText = queryString;
            command.ExecuteNonQuery();
        }
        public SqlCommand GetCommand(string sql, SqlParameter[] pa, bool hasStore)
        {
            conn = new SqlConnection(connectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            command = new SqlCommand(sql, conn);
            if (hasStore)
            {
                command.CommandType = CommandType.StoredProcedure;
            }
            AddParameter(command, pa);
            return command;
        }
        public SqlCommand GetCommandNonParameter(string sql, bool hasStore)
        {
            conn = new SqlConnection(connectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            command = new SqlCommand(sql, conn);
            if (hasStore)
            {
                command.CommandType = CommandType.StoredProcedure;
            }

            return command;
        }

        private void AddParameter(SqlCommand command, SqlParameter[] listPa)
        {
            foreach (SqlParameter pa in listPa)
            {
                command.Parameters.Add(pa);
            }
        }

        // kiểm tra sqldatareader có column muốn kiểm tra ko
        public static bool HasColumn(IDataRecord dr, string columnName)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return true;
            }
            return false;
        }
    }
}